package com.sysmind.ravi.margin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sysmind.ravi.margin.dao.OrderDao;
import com.sysmind.ravi.margin.model.Order;
import com.sysmind.ravi.margin.model.Stock;
import com.sysmind.ravi.margin.model.User;
import com.sysmind.ravi.margin.utils.JDBCHelper;

public class OrderDaoImpl implements OrderDao
{	JDBCHelper jhelp;
	Connection conn = null;
	PreparedStatement pstmt = null;
	Statement stmt = null;
	ResultSet rs = null;

	@Override
	public boolean addOrder(Order order, Stock stock, User user)
	{	jhelp =  new JDBCHelper();
		conn = jhelp.getConnection();
		String sql1 = "select order_seq.nextval as order_value from dual";
		String sql2 = "insert into orderinfo values(?,?,?,?,?,?,?)";
		//String sql2 = "insert into orderinfo values(?,?,?)";
		
		try 
		{	//conn.setAutoCommit(false);
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			while(rs.next())
			{
				//System.out.println("inside the 2nd sql....");
				pstmt = conn.prepareStatement(sql2);
				
				pstmt.setInt(1,rs.getInt("order_value"));
				pstmt.setInt(2, user.getUserId());
				pstmt.setInt(3,stock.getStockId());
				pstmt.setInt(4, order.getNumShares());
				pstmt.setInt(5, order.getOrderType());
				pstmt.setDouble(6, order.getOrderPrice());
				pstmt.setLong(7, order.getOrderTimeStamp());
				pstmt.executeUpdate();
				conn.commit();
			}
			return true;			
		}
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally
		{
			sql1 = sql2 = null;
			jhelp.closeConnection();
			jhelp = null;
			pstmt = null;
			stmt = null;
		}
		// TODO Auto-generated method stub
		//return false;
	}

	@Override
	public List<Order> getAllOrders(User user) 
	{	List <Order> orderList = null;
		// TODO Auto-generated method stub
		jhelp = new JDBCHelper();
		conn = jhelp.getConnection();
		int rowCount = getNumRows(user);
		
		if(rowCount > 0)
		{	String sql = "Select * from orderinfo where user_id = "+user.getUserId();
			orderList = new ArrayList<Order>();
			
			try 
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				
				while(rs.next())
				{	Order order = new Order();
					order.setOrderId(rs.getInt("order_id"));
					order.setNumShares(rs.getInt("num_shares"));
					order.setOrderPrice(rs.getDouble("order_price"));
					order.setOrderTimeStamp(rs.getLong("order_timestamp"));
					order.setOrderType(rs.getInt("Order_type"));
					orderList.add(order);
					order = null;
				}
				return orderList;	
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				jhelp.closeConnection();
				jhelp = null;
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				stmt = null;
				rs = null;
			}
		}//End of if
		else
		{
			return null;
		}
		
		
	}

	@Override
	public List<Order> getAllOrders(User user, Stock stock) 
	{
		List <Order> orderList = null;
		// TODO Auto-generated method stub
		jhelp = new JDBCHelper();
		conn = jhelp.getConnection();
		int rowCount = getNumRows(user,stock);
		
		if(rowCount > 0)
		{	String sql = "Select * from orderinfo where user_id = " +
				""+user.getUserId()+" and stock_id = "+stock.getStockId();
			orderList = new ArrayList<Order>();
			
			try 
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				
				while(rs.next())
				{	Order order = new Order();
					order.setOrderId(rs.getInt("order_id"));
					order.setNumShares(rs.getInt("num_shares"));
					order.setOrderPrice(rs.getDouble("order_price"));
					order.setOrderTimeStamp(rs.getLong("order_timestamp"));
					order.setOrderType(rs.getInt("Order_type"));
					orderList.add(order);
					order = null;
				}
				return orderList;	
			}
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			finally
			{
				jhelp.closeConnection();
				jhelp = null;
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				stmt = null;
				rs = null;
			}
		}//End of if
		else
		{
			return null;
		}
	}
	
	public int getNumRows(User user)
	{	JDBCHelper jhelp = new JDBCHelper();
		Connection conn = jhelp.getConnection();
		String sql = "SELECT COUNT(*) AS rowcount FROM orderinfo where user_id = "+user.getUserId();
		int rowCount  = 0;
		try 
		{	Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				rowCount = rs.getInt("rowcount");
			}
			return rowCount;
		}
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;			
		}
	}
	

	public int getNumRows(User user, Stock stock)
	{	JDBCHelper jhelp = new JDBCHelper();
		Connection conn = jhelp.getConnection();
		String sql = "SELECT COUNT(*) AS rowcount FROM orderinfo where user_id = " +
				""+user.getUserId()+" and stock_id = "+stock.getStockId();
		int rowCount  = 0;
		try 
		{	Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				rowCount = rs.getInt("rowcount");
			}
			return rowCount;
		}
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;			
		}
	}
}
